另外一個我比較常用的例子是，用 Class 來寫 Table 的對應程式。
（不過應該有這種 Tool 可以幫您將 DB 的 Table 自動產生 Class 程式碼的）

不過因為物件的溝通是靠訊息及 Event 的傳遞，如果欄位太多，則對於物件屬性的存取反而會影響到 Performance（用 Debug Trace 一下就知道是怎麼回事了）。

那就讓我們就進入程式吧！
假設我有一個員工的資料表，Employee

CREATE TABLE [dbo].[Employee] (
	[empy_id] [varchar] (6) NOT NULL ,
	[empy_nm] [varchar] (8) NOT NULL ,
	[dep_id] [varchar] (6) NOT NULL ,
	[status] [char] (1) NOT NULL ,
	[role] [char] (6) NOT NULL ,
	[email] [varchar] (50) NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Employee] ADD 
	CONSTRAINT [PK_Employee] PRIMARY KEY  CLUSTERED 
	(
		[empy_id]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

有 Employee ID, Employee Name, Department ID, Role, Email, Status 等欄位。

我會建構一個 Class，clsEmployee 來與之對應：

Imports System.Data.SqlClient

Public Class clsEmployee
  Private m_empy_id As String
  Private m_empy_nm As String
  Private m_dep_id As String
  Private m_role As String
  Private m_email As String
  Private m_status As String

  Public Property empy_id() As String
    Get
      Return m_empy_id
    End Get
    Set(ByVal value As String)
      m_empy_id = Value
    End Set
  End Property

  Public Property empy_nm() As String
    Get
      Return m_empy_nm
    End Get
    Set(ByVal value As String)
      m_empy_nm = value
    End Set
  End Property

  Public Property dep_id() As String
    Get
      Return m_dep_id
    End Get
    Set(ByVal value As String)
      m_dep_id = value
    End Set
  End Property

  Public Property role() As String
    Get
      Return m_role
    End Get
    Set(ByVal value As String)
      m_role = value
    End Set
  End Property

  Public Property email() As String
    Get
      Return m_email
    End Get
    Set(ByVal value As String)
      m_email = value
    End Set
  End Property

  Public Property status() As String
    Get
      Return m_status
    End Get
    Set(ByVal value As String)
      m_status = value
    End Set
  End Property

  Public Sub GetData(ByVal uid As String)
    Dim conn As New SqlConnection
    Dim cmd As New SqlCommand
    Dim dr As SqlDataReader

    Try

      conn.ConnectionString = My.Settings.ConnectionString
      conn.Open()

      cmd.Connection = conn

      cmd.CommandText = "select * from Employee where empy_id='" + uid + "'"
      dr = cmd.ExecuteReader()

      Do While dr.Read()
        m_empy_id = dr.GetString(dr.GetOrdinal("empy_id"))
        m_empy_nm = dr.GetString(dr.GetOrdinal("empy_nm"))
        m_dep_id = dr.GetString(dr.GetOrdinal("dep_id"))
        m_role = dr.GetString(dr.GetOrdinal("role"))
        m_email = dr.GetString(dr.GetOrdinal("email"))
        m_status = dr.GetString(dr.GetOrdinal("status"))
      Loop

      dr.Close()
    Catch ex As Exception
      Throw New System.Exception(ex.Message + vbCrLf + ex.StackTrace)
    Finally
      If conn.State = ConnectionState.Open Then
        conn.Close()
      End If

      dr = Nothing
      conn = Nothing
    End Try
  End Sub

  Public Sub SaveData()
    Dim conn As New SqlConnection
    Dim cmd As New SqlCommand
    Dim dr As SqlDataReader
    'Dim trans As SqlTransaction

    Try

      conn.ConnectionString = My.Settings.ConnectionString
      conn.Open()

      'trans = conn.BeginTransaction()

      cmd.Connection = conn
      'cmd.Transaction = trans

      cmd.CommandText = "select * from Employee where empy_id='" + empy_id + "'"
      dr = cmd.ExecuteReader()
      If dr.HasRows Then
        dr.Close()
        cmd.CommandText = "delete from Employee where empy_id='" + empy_id + "'"
        cmd.ExecuteNonQuery()
      End If

      If Not dr.IsClosed Then dr.Close()

      cmd.CommandText = "insert into Employee (empy_id,empy_nm,dep_id,role,email,status) values (?,?,?,?,?,?)"
      cmd.Parameters.Clear()
      cmd.Parameters.Add(New SqlParameter("?", m_empy_id))
      cmd.Parameters.Add(New SqlParameter("?", m_empy_nm))
      cmd.Parameters.Add(New SqlParameter("?", m_dep_id))
      cmd.Parameters.Add(New SqlParameter("?", m_role))
      cmd.Parameters.Add(New SqlParameter("?", m_email))
      cmd.Parameters.Add(New SqlParameter("?", m_status))
      cmd.ExecuteNonQuery()

      'trans.Commit()

    Catch ex As Exception
      'If Not trans Is Nothing Then
      '  trans.Rollback()
      'End If
      Throw New System.Exception(ex.Message + vbCrLf + ex.StackTrace)
    Finally
      If conn.State = ConnectionState.Open Then
        conn.Close()
      End If

      'trans = Nothing
      dr = Nothing
      conn = Nothing
    End Try
  End Sub
End Class

我可以在程式中宣告一個 Global 的物件，讓程式可以直接使用他，在 User 登入後，即將使用者的相關資料抓出來，存放於物件的屬性中（GetData()），如果使用者更改基本資料，那麼我也可以透過 SaveData() 將更新的資料回存到 Table 中，這樣在我的程式中，就不需要去撰寫那麼多的 SQL 存取指令，只要針對物件操作即可。

    Dim oEmp As New clsEmployee

    oEmp.GetData("A0001")

    MsgBox(oEmp.email)

執行結果：

如果這個 Class 會經常用到，那麼將他做成 DLL 或者加入目前的專案，就可以重複使用了。

本系列文章